package com.creating.mict.common.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.naming.NamingException;

public class OperOrcl {

	private static Connection conn;
	
	String url = "";
	String driver = "";
	String user = "";
	String password = "";
	

	public OperOrcl()
	  {
	    InputStream is = super.getClass().getResourceAsStream("/db.properties");
	    Properties dbProps = new Properties();
	    try {
	      dbProps.load(is);
	    } catch (Exception e) {
	      System.err.println("不能读取属性文件,请确保db.properties在CLASSPATH指定的路径中");
	      return;
	    }
	    this.url = dbProps.getProperty("url"); 
	    this.driver = dbProps.getProperty("driver"); 
	    this.user = dbProps.getProperty("username");
	    this.password = dbProps.getProperty("password");
	  }

	// 连接池连接数据库
	public  Connection getConn() throws SQLException {
		try {
//			
			// 加载jdbc-odbc bridge驱动程序
			Class.forName(this.driver);
			// 与url指定的数据源建立连接
			conn = DriverManager.getConnection(this.url, this.user, this.password);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return conn;
	}

	// 关闭数据库
	public void close(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	// 关闭数据库
	public void closeconn() throws SQLException, NamingException {
		if (!(conn == null || conn.isClosed()))
			conn.close();
	}

	// 查询
	public  ResultSet select(String sql) throws SQLException, NamingException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			if (conn == null || conn.isClosed()) {
				getConn();
			}
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			if (stmt != null)
				stmt.close();

			if (conn != null)
				conn.close();

		} catch (Exception exception) {
			if (stmt != null)
				stmt.close();

			if (conn != null)
				conn.close();
		}
		return rs;
	}

	// 查询 返回List
	@SuppressWarnings( { "rawtypes", "unchecked" })
	public List executeQuery(String sql) throws Exception {
		List list = new ArrayList();

		ResultSet rs = null;
		try {
			rs = this.select(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map map = new HashMap();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					// System.out.println("field[" + rsmd.getColumnName(i) +
					// "]:"
					// + rs.getObject(i));
					map.put(rsmd.getColumnName(i).toLowerCase(), rs
							.getObject(i));
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (conn != null)
				conn.close();
		}
		return list;
	}

	/**
	 * 根据sql更新数据库 返回影响的记录数
	 * 
	 * @param sql
	 *            SQL执行语句只接受UPDATE,INSERT,DELETE
	 */
	public  int getUpdate(String sql) throws SQLException, Exception {
		int i;
		Statement stmt = null;
		try {
			if (conn == null || conn.isClosed()) {
				getConn();
			}
			stmt = conn.createStatement();
			i = stmt.executeUpdate(sql);
		} catch (SQLException se) {
			se.printStackTrace();
			throw new SQLException("SQLException occured in getUpdate().");
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Unknown error in getUpdate().");
		} finally {
			if (conn != null)
				conn.close();
		}
		return i;
	}

	/**
	   * 返回查询行数
	  * @Title: queryCount 
	  * @Description: TODO(这里用一句话描述这个方法的作用) 
	  * @param @param sql
	  * @param @return
	  * @param @throws SQLException
	  * @param @throws NamingException 
	  * @return int    返回类型 
	  * @throws
	   */
	  public int queryCount(String sql)
	    throws SQLException, NamingException
	  {
		  Statement stmt = null;
		  ResultSet rs = null;
	    try
	    {
    	  if (conn == null || conn.isClosed()) {
			  getConn();
		  }
    	  stmt = conn.createStatement();
	      rs = stmt.executeQuery(sql);
	      rs.next();
	      return rs.getInt(1);
	    } catch (SQLException e) {
	      System.out.println("\n***SQLException 执行sql语句出错：" + sql + " ***\n" + 
	        e.getMessage());
	    } finally {
	      stmt.close();
	      if (conn != null)
				conn.close();
	    }
	    return 0;
	  }
//	
//	public static void main(String[] args) throws Exception {
//	      // 定义了数据库连接串
//		  String dbUrl = "jdbc:sqlserver://192.168.1.111:1433;DatabaseName=mybserver"; 
//	      // 数据库的用户名
//		  String user = "sa"; 
//		  // 数据库的用户口令
//		  String password = "dwbjk_server"; 
//		  // 加载jdbc-odbc bridge驱动程序
//		  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
//		  // 与url指定的数据源建立连接
//		  Connection c = DriverManager.getConnection(dbUrl, user, password); 
//		  // 采用Statement进行查询
//		  Statement s = c.createStatement();
//		  ResultSet r = select("select top 10 * from fyt_etxxb0"); 
//		  
//		  String bir = "";
//		  String pym = "";
//		  String id = "";
//		  int i = 0;
//		  while (r.next()) { // 打印字段信息
//			  i++;
//			  System.out.println(i);
////			  bir = r.getString("fbdate");
////			  if(bir == null || bir.equals("null")) {
////				  bir = "";
////			  }
////			  pym = r.getString("fpym_name");
////			  id = r.getString("fk_card_no");
////			  s.executeUpdate("update tjhmy_yyjl c set c.fname_pym='" + pym + "'  where c.fcard_no='" + id + "'");
//		  }
//		  // 关闭Statement，其上的ResultSet也将关闭
//		  s.close(); 
//		  
//		  
//		  
//		  
//		  
////		    Statement stmt = null;
////		    ResultSet rs = null;
////			try{
////				Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
////				Connection conn = DriverManager.getConnection(
////						"jdbc:microsoft:sqlserver://127.0.0.1:1433; DatabaseName=mybserver",
////						"sa", "mhis");
////				// 采用Statement进行查询
////				stmt = conn.createStatement();
////				rs = stmt.executeQuery("select top 10 * from zonecode "); 
////				while(rs.next()) { // 打印字段信息
////					System.out.println(rs.getString("cnname"));  
////				}
////				// 关闭Statement，其上的ResultSet也将关闭
////				rs.close(); 
////			} catch (ClassNotFoundException e) {
////				// TODO Auto-generated catch block
////				rs.close(); 
////				e.printStackTrace();
////			}
//		  
//		  
//		  
//		  
//	}
}
